// MaterialPurchaseApplicationDao.java
package org.example.managementsystem.repository;

import org.example.managementsystem.model.MaterialPurchaseApplication;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.List;

@Repository
public class MaterialPurchaseApplicationDao {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public MaterialPurchaseApplicationDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<MaterialPurchaseApplication> findAll() {
        String sql = "SELECT * FROM material_purchase_application";
        return jdbcTemplate.query(sql, new MaterialPurchaseRowMapper());
    }

    public MaterialPurchaseApplication findById(Integer purchaseId) {
        String sql = "SELECT * FROM material_purchase_application WHERE purchase_id = ?";
        List<MaterialPurchaseApplication> applications = jdbcTemplate.query(sql, new MaterialPurchaseRowMapper(), purchaseId);
        return applications.isEmpty() ? null : applications.get(0);
    }

    public MaterialPurchaseApplication save(MaterialPurchaseApplication application) {
        if (application.getPurchaseId() == null) {
            String insertSql = "INSERT INTO material_purchase_application " +
                    "(material_name, material_type, provider_name, factory_address, storage_date, " +
                    "specification, contact_person, contact_phone, material_desc, purchase_quantity, " +
                    "applicant_job_number, purchase_desc, is_approved) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
                setApplicationParameters(ps, application);
                return ps;
            }, keyHolder);

            application.setPurchaseId(keyHolder.getKey().intValue());
        } else {
            String updateSql = "UPDATE material_purchase_application SET " +
                    "material_name = ?, material_type = ?, provider_name = ?, factory_address = ?, " +
                    "storage_date = ?, specification = ?, contact_person = ?, contact_phone = ?, " +
                    "material_desc = ?, purchase_quantity = ?, applicant_job_number = ?, " +
                    "purchase_desc = ?, is_approved = ? WHERE purchase_id = ?";
            jdbcTemplate.update(updateSql,
                    application.getMaterialName(),
                    application.getMaterialType().name(),
                    application.getProviderName(),
                    application.getFactoryAddress(),
                    application.getStorageDate(),
                    application.getSpecification(),
                    application.getContactPerson(),
                    application.getContactPhone(),
                    application.getMaterialDesc(),
                    application.getPurchaseQuantity(),
                    application.getApplicantJobNumber(),
                    application.getPurchaseDesc(),
                    application.getIsApproved(),
                    application.getPurchaseId());
        }
        return application;
    }

    private void setApplicationParameters(PreparedStatement ps, MaterialPurchaseApplication application) throws SQLException {
        ps.setString(1, application.getMaterialName());
        ps.setString(2, application.getMaterialType().name());
        ps.setString(3, application.getProviderName());
        ps.setString(4, application.getFactoryAddress());
        ps.setDate(5, new Date(application.getStorageDate().getTime()));
        ps.setString(6, application.getSpecification());
        ps.setString(7, application.getContactPerson());
        ps.setString(8, application.getContactPhone());
        ps.setString(9, application.getMaterialDesc());
        ps.setInt(10, application.getPurchaseQuantity());
        ps.setString(11, application.getApplicantJobNumber());
        ps.setString(12, application.getPurchaseDesc());
        ps.setBoolean(13, application.getIsApproved() != null ? application.getIsApproved() : false);
    }

    private static class MaterialPurchaseRowMapper implements RowMapper<MaterialPurchaseApplication> {
        @Override
        public MaterialPurchaseApplication mapRow(ResultSet rs, int rowNum) throws SQLException {
            MaterialPurchaseApplication app = new MaterialPurchaseApplication();
            app.setPurchaseId(rs.getInt("purchase_id"));
            app.setMaterialName(rs.getString("material_name"));
            app.setMaterialType(MaterialPurchaseApplication.MaterialType.valueOf(rs.getString("material_type")));
            app.setProviderName(rs.getString("provider_name"));
            app.setFactoryAddress(rs.getString("factory_address"));
            app.setStorageDate(rs.getDate("storage_date"));
            app.setSpecification(rs.getString("specification"));
            app.setContactPerson(rs.getString("contact_person"));
            app.setContactPhone(rs.getString("contact_phone"));
            app.setMaterialDesc(rs.getString("material_desc"));
            app.setPurchaseQuantity(rs.getInt("purchase_quantity"));
            app.setApplicantJobNumber(rs.getString("applicant_job_number"));
            app.setPurchaseDesc(rs.getString("purchase_desc"));
            app.setIsApproved(rs.getBoolean("is_approved"));
            return app;
        }
    }
}